package csc.fresher.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.DepositAccountDao;
import csc.fresher.entity.AccountState;
import csc.fresher.entity.CustomerAccount;
import csc.fresher.entity.DepositAccount;
import csc.fresher.utils.DBUtils;

@Repository
public class DepositAccountDaoImpl implements DepositAccountDao {

	@Autowired
	private DataSource dataSource;
	private static final Logger logger = Logger
			.getLogger(SavingAccountDaoImpl.class);

	@Override
	public CustomerAccount getCustomerAccountByID(long cutomerAccountID) {
		String query = "SELECT * FROM `DepositAccount` WHERE `DepositAccount`.`DepositAccountNumber`=?";
		DepositAccount depositAccount = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, cutomerAccountID);
			rs = preparedStatement.executeQuery();
			depositAccount = getDepositAccountResultSet(rs);

		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			DBUtils.closeResultSetConnection(connection, preparedStatement, rs, logger);
		}
		return depositAccount;
	}

	@Override
	public CustomerAccount getCustomerAccountByCardNumberID(long cardNumberID) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void create(DepositAccount depositAccount) {
		String query = "INSERT INTO `DepositAccount` (`AccountType`,`DepositAccountBalance`,`isRepeatable`,`CreatedDate`,`AccountState`, `Customer_CustomerID`,`AccountTransactionState`) VALUES (?,?,?,?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setByte(1, depositAccount.getAccountType());
			preparedStatement.setDouble(2, depositAccount.getAccountBalance());
			preparedStatement.setBoolean(3, depositAccount.getRepeatable());
			preparedStatement.setTimestamp(4, depositAccount.getCreatedDate());
			preparedStatement.setString(5, depositAccount.getAccountState()
					.getState());
			preparedStatement.setLong(6, depositAccount.getCustomerID());
			preparedStatement.setString(7, depositAccount.getAccountTransactionState().getState());
			preparedStatement.execute();
			connection.commit();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
	}

	@Override
	public void update(DepositAccount depositAccount) {
		String query = "UPDATE `DepositAccount` "
				+ " SET  `DepositAccount`.`AccountType`=?, `DepositAccount`.`DepositAccountBalance`=?, `DepositAccount`.`isRepeatable`=?, `DepositAccount`.`AccountState`=?, `DepositAccount`.`Customer_CustomerID`=? "
				+ "WHERE `DepositAccount`.`DepositAccountNumber`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setByte(1, depositAccount.getAccountType());
			preparedStatement.setDouble(2, depositAccount.getAccountBalance());
			preparedStatement.setBoolean(3, depositAccount.getRepeatable());
			preparedStatement.setTimestamp(4, depositAccount.getCreatedDate());
			preparedStatement.setString(5, depositAccount.getAccountState()
					.getState());
			preparedStatement.setLong(6, depositAccount.getCustomerID());
			preparedStatement.execute();
			connection.commit();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
	}

	@Override
	public void delete(DepositAccount depositAccount) {
		String query = "UPDATE `DepositAccount` "
				+ " SET  `DepositAccount`.`AccountState`=? "
				+ "WHERE `DepositAccount`.`DepositAccountNumber`=? ";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);

			preparedStatement.setString(1, AccountState.REMOVABLE.getState());
			preparedStatement.setLong(2, depositAccount.getAccountNumber());
			preparedStatement.execute();
			connection.commit();
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
				logger.error(e1);
			}
		} finally {
			DBUtils.closeConnection(connection, preparedStatement, logger);
		}
	}

	private DepositAccount getDepositAccountResultSet(ResultSet rs)
			throws SQLException {
		DepositAccount depositAccount = new DepositAccount();
		depositAccount.setAccountNumber(rs.getLong("DepositAccountNumber"));
		depositAccount.setAccountType(rs.getByte("AccountType"));
		depositAccount.setAccountBalance(rs.getDouble("DepositAccountBalance"));
		depositAccount.setRepeatable(rs.getBoolean("isRepeatable"));
		depositAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
		depositAccount.setAccountState(AccountState.valueOf(rs
				.getString("AccountState")));
		depositAccount.setCustomerID(rs.getLong("Customer_CustomerID"));
		return depositAccount;
	}

	// Minh Dao part
	@Override
	public void getAccountBalance(DepositAccount depositAccount) {
		// String query =
		// "SELECT `DepositAccountBalance` FROM `DepositAccount` WHERE `DepositAccount`.`IDCardNumber` = `?`";
		// DepositAccount depositAccount = null;
		// Connection connection = null;
		// PreparedStatement preparedStatement = null;
		// ResultSet rs = null;
		// try {
		// connection = dataSource.getConnection();
		// preparedStatement = connection.prepareStatement(query);
		// preparedStatement.setLong(1, cutomerAccountID);
		// rs = preparedStatement.executeQuery();
		// depositAccount = getDepositAccountResultSet(rs);
		//
		// } catch (SQLException e) {
		// logger.error(e);
		// e.printStackTrace();
		// } finally {
		// try {
		// if (preparedStatement != null) {
		// preparedStatement.close();
		// }
		// if (connection != null) {
		// connection.close();
		// }
		// if (rs != null) {
		// rs.close();
		// }
		// } catch (SQLException e) {
		// logger.error(e);
		// e.printStackTrace();
		// }
		// }
	}

	// Nghia part

	public Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return DriverManager.getConnection(
					"jdbc:mysql://20.203.182.23:3306/finalproject", "root",
					"root");
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<DepositAccount> getAccountByState(String accountState) {
		List<DepositAccount> resultList = new ArrayList<DepositAccount>();
		String query = "SELECT * FROM `DepositAccount` WHERE `AccountState`=?";
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {

			conn = getConnection();
			preparedStatement = conn.prepareStatement(query);
			preparedStatement.setString(1, accountState);

			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				DepositAccount depositAccount = new DepositAccount();
				depositAccount.setAccountNumber(rs
						.getLong("DepositAccountNumber"));
				depositAccount.setAccountType(rs.getByte("AccountType"));
				depositAccount.setAccountBalance(rs
						.getDouble("DepositAccountBalance"));
				depositAccount.setRepeatable(rs.getBoolean("isRepeatable"));
				depositAccount.setCreatedDate(rs.getTimestamp("CreatedDate"));
				depositAccount.setAccountState(AccountState.valueOf(rs
						.getString("AccountState").toUpperCase()));
				depositAccount.setCustomerID(rs.getLong("Customer_CustomerID"));
				resultList.add(depositAccount);
			}
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return resultList;
	}

	public static void main(String[] agrs) {
		DepositAccountDao depAccountDao = new DepositAccountDaoImpl();
		List<DepositAccount> resultList = depAccountDao
				.getAccountByState("active");

		for (int i = 0; i < resultList.size(); i++) {
			DepositAccount depAccount = resultList.get(i);
			System.out.println(depAccount.getAccountBalance());
		}
	}

	@Override
	public List<CustomerAccount> getListCutomerAccountsbyCardNumberID(
			long cardNumberID) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public CustomerAccount getSavingAccountByCustomerID(long cutomerAccountID) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean checkActiveCustomerAccountTransactionState(long customerAccountID) {
		// TODO Auto-generated method stub
		return false;
	}

}
